﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_ThuvienmonanDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        //ClsConnection ClsCnn = new ClsConnection();
        //
        public DataSet GetByTopN_Id()
        {
            string NamePrc = " SELECT Max( TblDm_Thuvienmonan.Id) as Id FROM TblDm_Thuvienmonan";
            DataSet Dset = new DataSet();

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset);

            return Dset;
        }

        public DataSet FindSameName(int LoaimonId, int DotuoiId, string VarTen)
        {
            string NamePrc = QueryString.TBLDM_THUVIENMONAN_FINDNAMESAMEPRC.Replace("@VarTen", VarTen).Replace("@LoaimonId", LoaimonId.ToString()).Replace("@DotuoiId", DotuoiId.ToString());
            string NameTable = "TblDm_Thuvienmonan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset;
        }
        //
        //
        public BindingSource SelectAllByLoaimonIdandByName(int LoaimonId, string Tenmonan)
        {
            QueryString.SP_THUVIENMONAN_GETBYLOAIMONIDANDTENMONAN = "SELECT * FROM [TblDm_Thuvienmonan] WHERE [LoaimonId] = " + LoaimonId + " and Tenmonan like '%" + Tenmonan + "%'";
            string NamePrc = QueryString.SP_THUVIENMONAN_GETBYLOAIMONIDANDTENMONAN;
            string NameTable = "TblDm_Thuvienmonan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            //Cmm.Parameters.Add(new OleDbParameter("@LoaimonId", LoaimonId));
            //Cmm.Parameters.Add(new OleDbParameter("@Tenmonan", Tenmonan));

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }
        public BindingSource SelectAllByLoaimonId(int LoaimonId)
        {
            string NamePrc = QueryString.SP_THUVIENMONAN_GETBYLOAIMONANID;
            string NameTable = "TblDm_Thuvienmonan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@LoaimonId", LoaimonId));

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }
        public BindingSource SelectAllByLoaimonId(int LoaimonId, int DotuoiId)
        {
            string NameTable = "TblDm_Thuvienmonan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();

            //NamePrc = QueryString.SP_THUVIENMONAN_GETBYID1;
            string NamePrc = " SELECT TblDm_Thuvienmonan.Id, TblDm_Thuvienmonan.Tenmonan, "
            + " TblDm_Thuvienmonan.DotuoiId, TblDm_Thuvienmonan.LoaimonId, "
            + " TblDm_Thuvienmonan.Motasudung, TblDm_Thuvienmonan.Nguyenlieu, "
            + " TblDm_Thuvienmonan.Thuchien, TblDm_Thuvienmonan.Hinhanh, "
            + " TblDm_Thuvienmonan.Chuthich, TblDm_Thuvienmonan.Trichnguon "
            + " FROM TblDm_Thuvienmonan inner join TblDm_Loaimon on TblDm_Thuvienmonan.LoaimonId = TblDm_Loaimon.Id "
            + " WHERE TblDm_Thuvienmonan.LoaimonId= " + LoaimonId.ToString();

            if (DotuoiId == 0)
            {
                NamePrc += " and TblDm_Thuvienmonan.DotuoiId = 0 ";
            }
            else
            {
                NamePrc += " and (TblDm_Thuvienmonan.DotuoiId = " + DotuoiId + " or TblDm_Thuvienmonan.DotuoiId  =0) ";
            }
            NamePrc += " order by TblDm_Thuvienmonan.DotuoiId desc ";

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;

            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }
        public BindingSource SelectAllByLoaimonId()
        {
            string NameTable = "TblDm_Thuvienmonan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            OleDbCommand Cmm = new OleDbCommand();

            //NamePrc = QueryString.SP_THUVIENMONAN_GETBYID1;
            string NamePrc = " SELECT TblDm_Thuvienmonan.Id, TblDm_Thuvienmonan.Tenmonan, "
            + " TblDm_Thuvienmonan.DotuoiId, TblDm_Thuvienmonan.LoaimonId, "
            + " TblDm_Thuvienmonan.Motasudung, TblDm_Thuvienmonan.Nguyenlieu, "
            + " TblDm_Thuvienmonan.Thuchien, TblDm_Thuvienmonan.Hinhanh, "
            + " TblDm_Thuvienmonan.Chuthich, TblDm_Thuvienmonan.Trichnguon "
            + " FROM TblDm_Thuvienmonan inner join TblDm_Loaimon on TblDm_Thuvienmonan.LoaimonId = TblDm_Loaimon.Id ";
            NamePrc += " order by TblDm_Thuvienmonan.DotuoiId desc ";

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;

            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }

        public void Insert(string Tenmonan, int LoaimonId, int DotuoiId, string Motasudung, string Nguyenlieu, string Thuchien, string Hinhanh, string Chuthich, string Trichnguon)
        {
            string NamePrc = "INSERT INTO TblDm_Thuvienmonan(Tenmonan, DotuoiId, LoaimonId, Motasudung, Nguyenlieu, Thuchien, Hinhanh, Chuthich, Trichnguon) VALUES ( '" + Tenmonan + "', " + DotuoiId.ToString() + ", " + LoaimonId.ToString() + ", '" + Motasudung + "', '" + Nguyenlieu + "', '" + Thuchien + "', '" + Hinhanh + "', '" + Chuthich + "', '" + Trichnguon + "') ";
            OleDbCommand Cmm = new OleDbCommand();

            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Edit(int Id, string Tenmonan, int DotuoiId, int LoaimonId, string Motasudung, string Nguyenlieu, string Thuchien, string Hinhanh, string Chuthich, string Trichnguon)
        {
            string NamePrc = QueryString.SP_THUVIENMONAN_UPDATE;
            OleDbCommand Cmm = new OleDbCommand();

            ClsConnection.OleDbDAdpter(NamePrc.Replace("@Id", Id.ToString()).Replace("@LoaimonId", LoaimonId.ToString()).Replace("@DotuoiId", DotuoiId.ToString()).Replace("@Motasudung", Motasudung).Replace("@Nguyenlieu", Nguyenlieu).Replace("@Tenmonan", Tenmonan).Replace("@Thuchien", Thuchien).Replace("@Hinhanh", Hinhanh).Replace("@Chuthich", Chuthich).Replace("@Trichnguon", Trichnguon), Cmm);
        }
        public void Del(int Id)
        {
            string NamePrc = "DELETE FROM [TblDm_Thuvienmonan] WHERE Id =" + Id.ToString();
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
    }
}
